import datetime
import pymysql
from faker import Faker

host='localhost'
port=3306
database='duxiaoman'
user='root'
password='123456'
charset='utf8'

def update(sql,param):
        con =   pymysql.connect(
                host = host,
                port = port,
                user = user,
                password = password,
                database = database,
                charset = charset
        )
        cursor = con.cursor()
        cursor.execute(sql,param)
        con.commit()
        cursor.close()
        con.close()


def select(sql,param):
        con =pymysql.connect(
                host=host,
                port=port,
                user = user,
                password = password,
                database = database,
                charset = charset
        )
        cursor = con.cursor()
        cursor.execute(sql,param)
        data = cursor.fetchall()
        con.commit()
        cursor.close()
        con.close()
        return data


def show_dict1():
        print('ID            类别            账户            金额            时间            说明')
        sql = 'SELECT * FROM bill'
        parm = ()
        date = select(sql,parm)

        for i in date:
                for j in i:
                        print('{}       \t'.format(j),end='')
                print()
        return date
# show_dict1()


def show_dict2():
        while 1:
                s = input('请输入查找内容：')
                print('ID            类别            账户            金额            时间            说明')
                sql = 'SELECT * FROM bill'
                parm = ()
                date = select(sql, parm)
                list1 = []
                for i in date:
                        for j in i:
                                if s == str(j):
                                        list1.append(i)
                if len(list1) > 0:
                        for i in list1:
                                for j in i:
                                        print('{}       \t'.format(j), end='')
                                print()
                else:print('没有该数据')

                if int(input('是否继续查找, YES请按：0, NO请按：1,请输入：')) == 1:
                        break

# show_dict2()

def into_dict1():

        while 1:

                s2 = input('类别:')
                s3 = input('账户:')
                s4 = int(input('金额:'))
                s5 = datetime.datetime.now().date()
                s5 = str(s5)

                s6 = input('说明:')
                sql = 'insert into bill (Type, Account, Money, Time, Text) VALUES (%s, %s, %s, %s, %s)'
                parm = (s2, s3, s4, s5, s6)
                update(sql, parm)
                show_dict1()
                if int(input('是否继续添加, YES请按：0, NO请按：1,请输入：')) == 1:
                        break

# into_dict1()

def update_dict1():
        while 1:
                show = show_dict1()
                try:
                        s1 = input('输入修改Id:')
                        s1 = int(s1)
                        s2 = input('输入修改位置：')
                        s3 = datetime.datetime.now().date()
                        s3 = str(s3)
                        s4 = input('输入修改值：')
                        if s2 == '类别':
                                s2 = 'Type'
                                sql = 'UPDATE bill SET Type = %s, Time = %s WHERE Id = %s '
                                parm = (s4, s3, s1)
                                update(sql, parm)
                        if s2 == '账户':
                                s2 = 'Account'
                                sql = 'UPDATE bill SET Account = %s, Time = %s WHERE Id = %s '
                                parm = (s4, s3, s1)
                                update(sql, parm)
                        if s2 == '金额':
                                s2 = 'Money'
                                sql = 'UPDATE bill SET Money = %s, Time = %s WHERE Id = %s '
                                parm = (s4, s3, s1)
                                update(sql, parm)
                        if s2 == '说明':
                                s2 = 'Text'
                                sql = 'UPDATE bill SET Text = %s, Time = %s WHERE Id = %s '
                                parm = (s4, s3, s1)
                                update(sql, parm)
                        if s2 == '时间':
                                s2 = 'Time'
                                sql = 'UPDATE bill SET Time = %s WHERE Id = %s '
                                parm = (s4, s1)
                                update(sql, parm)
                except Exception as e:
                        print(f"An error occurred: {e}")


                show_dict1()
                if int(input('是否继续修改, YES请按：0, NO请按：1,请输入：')) == 1:
                        break

# update_dict1()

def pop_dict1():
        date = show_dict1()
        list1 = []
        for i in date:
                for j in i:
                        print('{}       \t'.format(j),end='')
                print()

        s = input('请输入删除数据：')
        for i in range(len(date)):
                for j in range(len(date[i])):
                        if s == str(date[i][j]):
                                list1.append(date[i][0])
        sql = 'DELETE FROM bill WHERE Id = %s'
        for i in list1:
                try:
                        update(sql,i)
                except Exception as e:
                        print(f"An error occurred: {e}")
        date = show_dict1()

# pop_dict1()



def show_users(s1,s2):
        sql = 'SELECT * FROM users WHERE username = %s and userpass = %s'

        param = (s1, s2)
        try:
                data = select(sql,param)[0][0]
                return data
        except IndexError as e:
                return 0

# p = show_users()
# print(p)

def show_view1():
        print('1.添加账务     2.编辑账务     3.删除账务     4.查询账务     5.退出系统     ')
        print('请输入要操作的功能序号[1-5]:', end=' ')

def admin_login():
        while 1:
                show_view1()
                a = input()
                if a == '1':
                        into_dict1()
                elif a == '2':
                        updata_dict1()
                elif a == '3':
                        pop_dict1()
                elif a == '4':
                        s = show_dict1()
                elif a == '5':
                        print('系统退出成功！')
                        break
                else:
                        print('输入有误')

def user_login():
        while 1:
                print('1.查询全部  2.搜索查询 3.退出系统：', end='')
                b = input()
                if b == '1':
                        s = show_dict1()
                if b == '2':
                        show_dict2()
                if b == '3':
                        break



print('欢迎使用！')
s1 = input('请输入账号：')
s2 = input('请输入密码：')
login = show_users(s1, s2)
if login == 0:
        print('账号或密码错误，登入失败')
if login == 1:
        admin_login()
if login > 1:
        user_login()













